-- This scripts contains following pcakage's definition:
-- =============================================================================
-- dbms_metadata    F   get_ddl(varchar,varchar,varchar,varchar,varchar,varchar)
-- dbms_output      P   disable()
--                  P   enable(int4)
--                  P   new_line()
--                  P   put(text)
--                  P   put_line(text)
--                  P   get_line(text,int4)
--                  P   get_lines(text,int4)
-- dbms_random      P   initialize(int4)    -- deprecated
--                  F   normal()
--                  F   random()    -- deprecated
--                  P   seed(int4)
--                  P   seed(text)
--                  F   string(char,int4)
--                  P   terminate()    -- deprecated
--                  F   value()
--                  F   value(numeric,numeric)
-- dbms_job         P   broken(int8,bool,timestamp)
--                  P   change(int8,text,timestamp,text,int4,bool)
--                  P   instance(int8,int4,bool)
--                  P   interval(int8,text)
--                  P   next_date(int8,timestamp)
--                  P   remove(int8)
--                  P   run(int8,bool)
--                  P   submit(int8,text,timestamp,text,bool,int4,bool)
--                  P   user_export(int8,text)
--                  P   what(int8,text)
-- dbms_lob         P   append(inout bytea,bytea)  @todo
--                  P   append(inout text,text)  @todo
--                  P   close(inout bytea)  @todo
--                  P   close(inout text)  @todo
--                  P   converttoblob  @todo
--                  P   converttoclob  @todo
--                  P   copy  @todo
--                  F   getlength  @todo
--                  F   instr  @todo
--                  P   loadblobfromfile  @todo
--                  P   loadclobfromfile  @todo
--                  P   open  @todo
--                  P   read  @todo
--                  F   substr  @todo
--                  P   trim  @todo
--                  P   write  @todo
--                  P   writeappend  @todo

set client_min_messages='warning';


-- =============================================================================
-- Create Schemas
-- =============================================================================
do $$
declare
    l_cnt       bigint;
    l_schema    varchar(128);
begin
    foreach l_schema in array string_to_array('oracle,dbms_metadata,dbms_output,dbms_random', ',')
    loop
        select count(*) into l_cnt from pg_catalog.pg_namespace where nspname = l_schema;
        if l_cnt = 0
        then
            execute 'create schema '||l_schema;
        end if;
    end loop;
end;
$$ language plpgsql;


-- =============================================================================
-- Version Table/Function
-- =============================================================================
create table if not exists oracle.compat_version
(
    compat_type     varchar(10),    -- VIEW, FUNCTION, PROCEDURE
    object_name     varchar(128),   -- Compat object name
    object_version  varchar(10),    -- Compat object version
    constraint pk_compat_version primary key(compat_type, object_name)
);

drop function if exists oracle.drop_compat_package(varchar, varchar, varchar, varchar, varchar);
create or replace function oracle.drop_compat_package( p_object_type    varchar(10)
                                                     , p_package_name   varchar(128)
                                                     , p_object_name    varchar(128)
                                                     , p_object_version varchar(10)
                                                     , p_function_lang  varchar(16)  default 'plpgsql')
returns boolean
as $$
declare
    l_version    varchar(10);
    l_language   varchar(16);
    l_result     boolean      := 'true'; -- 是否可以在后续逻辑中继续创建这个对象
    l_operation  varchar(128);
begin
    CREATE temporary table if not exists temp_result
    (
        object_type     varchar(10),
        object_name     varchar(128),
        local_version   varchar(10),
        script_version  varchar(10),
        local_language  varchar(10),
        script_language varchar(10),
        operation       varchar(128)
    ) ON COMMIT PRESERVE ROWS;

    -- 字符串参数统一转小写
    p_object_type := lower(p_object_type);
    p_package_name := lower(p_package_name);
    p_object_name := lower(p_object_name);
    p_function_lang := lower(p_function_lang);

    select max(object_version) into l_version
      from oracle.compat_version
     where object_name = p_package_name||'.'||p_object_name
       and compat_type = p_object_type;

    -- 获取已有同名同参数函数/存储过程的语言，存入 l_language 变量
    if instr(p_object_name, '()') = 0    -- 有参数
    then
        select max(lanname) into l_language
          from (select l.lanname
                     , p.proname || '(' || string_agg(t.typname, ',' order by p.id) || ')' as full_name
                  from (select pronamespace
                             , proname
                             , prolang
                             , case when proallargtypes is null then proargtypes else proallargtypes end as proallargtypes
                             , generate_series(1, array_length(case when proallargtypes is null then proargtypes else proallargtypes end, 1)) as id
                             , unnest(case when proallargtypes is null then proargtypes else proallargtypes end) as protype
                          from pg_proc
                         where proname = substr(p_object_name, 1, instr(p_object_name, '(') - 1)
                       ) as p
                  join pg_namespace as n on p.pronamespace = n.oid
                  join pg_language as l on p.prolang = l.oid
                  join pg_type as t on p.protype = t.oid
                 where n.nspname = p_package_name
                 group by p.proname, p.proallargtypes, l.lanname
               ) as x
         where full_name = p_object_name;
    else    -- 无参数
        select max(l.lanname) into l_language
          from pg_proc as p
          join pg_namespace as n on p.pronamespace = n.oid
          join pg_language as l on p.prolang = l.oid
         where n.nspname = p_package_name
           and p.proname = replace(p_object_name, '()', '')  -- 移除尾部的 ()
           and oidvectortypes(p.proargtypes) is null;
    end if;

    if l_language is null
    then
        l_operation := 'Initial creation';
    elsif l_language != p_function_lang
    then
        l_result = 'false';
        l_operation := 'Skip due to language';
    elsif l_version < p_object_version
    then
        l_operation := 'Upgrade';
    else
        l_result = 'false';
        l_operation := 'Skip due to version';
    end if;

    if l_result
    then
        execute 'drop '||p_object_type||' if exists '
             || p_package_name||'.'||p_object_name
             || (case when p_object_type = 'FUNCTION' then ' cascade' else '' end);
        insert into oracle.compat_version
        values ( p_object_type
               , p_package_name||'.'||p_object_name
               , p_object_version)
        ON DUPLICATE KEY UPDATE object_version = p_object_version;
    else
        insert into oracle.compat_version
        values ( p_object_type
               , p_package_name||'.'||p_object_name
               , p_object_version)
        ON DUPLICATE KEY UPDATE NOTHING;
    end if;

    insert into temp_result values ( p_object_type
                                   , p_package_name||'.'||p_object_name
                                   , l_version
                                   , p_object_version
                                   , l_language
                                   , p_function_lang
                                   , l_operation);
    return l_result;
end;
$$ language plpgsql;


-- =============================================================================
-- All creations are running in ANONYMOUS BLOCK
-- =============================================================================
do $PKG_CREATION$
declare
    l_cnt    bigint;
begin
    -- =========================================================================
    -- dbms_metadata.get_ddl(varchar,varchar,varchar,varchar,varchar,varchar)
    -- =========================================================================
    begin
        if oracle.drop_compat_package('FUNCTION', 'dbms_metadata', 'get_ddl(varchar,varchar,varchar,varchar,varchar,varchar)', '1.1')
        then
            CREATE or replace FUNCTION dbms_metadata.get_ddl( object_type  VARCHAR
                                                            , name         VARCHAR
                                                            , schema       VARCHAR  DEFAULT  current_schema()
                                                            , version      VARCHAR  DEFAULT  'COMPATIBLE'  -- 暂不使用
                                                            , model        VARCHAR  DEFAULT  'ORACLE'      -- 暂不使用
                                                            , transform    VARCHAR  DEFAULT  'DDL')        -- 暂不使用
            RETURNS text IMMUTABLE AS $$
            declare
                l_oid           text;
                l_temp_result   text;
                l_temp_record   record;
                l_ddl           text;
            begin
                -- 纯大写的参数转为全小写
                if upper(object_type) = object_type
                then
                    object_type := lower(object_type);
                end if;
                if upper(name) = name
                then
                    name := lower(name);
                end if;
                if upper(schema) = schema
                then
                    schema := lower(schema);
                end if;

                -- 按照对象类型生成 DDL
                begin
                    if object_type = 'table'
                    then
                        select pg_get_tabledef(oid) into l_ddl
                          from pg_class
                         where relname = name
                           and relnamespace = (select oid
                                                 from pg_namespace
                                                where nspname = schema)
                           and relkind = 'r';
                    elsif object_type = 'view'
                    then
                        select 'CREATE OR REPLACE VIEW "'||schema||'"."'||name||' as '
                            || pg_get_viewdef(oid, true) into l_ddl
                          from pg_class
                         where relname = name
                           and relnamespace = (select oid
                                                 from pg_namespace
                                                where nspname = schema)
                           and relkind = 'v';
                    elsif object_type = 'index'
                    then
                        select pg_get_indexdef(oid, 0, true) into l_ddl
                          from pg_class
                         where relname = name
                           and relnamespace = (select oid
                                                 from pg_namespace
                                                where nspname = schema)
                           and relkind in ('i', 'I');
                    elsif object_type in ('constraint', 'ref_constraint')
                    then
                        select 'ALTER TABLE "'||schema||'"."'||cls.relname||'" ADD CONSTRAINT "'||name||'" '
                            || pg_get_constraintdef(con.oid, true) into l_ddl
                          from pg_constraint con
                          join pg_class cls on con.conrelid = cls.oid
                          join pg_namespace nsp on con.connamespace = nsp.oid
                         where con.conname = name
                           and nsp.nspname = schema;
                    elsif object_type = 'function'
                    then
                        select (pg_get_functiondef(oid)).definition into l_ddl
                          from pg_proc
                         where proname = name
                           and pronamespace = (select oid
                                                 from pg_namespace
                                                where nspname = schema)
                           and prokind != 'p';
                    elsif object_type = 'procedure'
                    then
                        select (pg_get_functiondef(oid)).definition into l_ddl
                          from pg_proc
                         where proname = name
                           and pronamespace = (select oid
                                                 from pg_namespace
                                                where nspname = schema)
                           and prokind = 'p';
                    elsif object_type = 'trigger'
                    then
                        select pg_get_triggerdef(t.oid, true) into l_ddl
                          from pg_trigger as t
                          join pg_class as c on t.tgrelid = c.oid
                          join pg_namespace as n on c.relnamespace = n.oid
                         where t.tgname = name
                           and n.nspname = schema;
                    elsif object_type in ('materialized_view', 'materialized view')
                    then
                        select 'CREATE MATERIALIZED VIEW "'||schema||'"."'||name||' as '
                            || pg_get_viewdef(oid, true) into l_ddl
                          from pg_class
                         where relname = name
                           and relnamespace = (select oid
                                                 from pg_namespace
                                                where nspname = schema)
                           and relkind = 'm';
                    elsif object_type = 'sequence'
                    then
                        for l_temp_record in execute 'select last_value
                                                           , start_value
                                                           , increment_by
                                                           , max_value
                                                           , min_value
                                                           , cache_value
                                                           , is_cycled
                                                        from "'||schema||'"."'||name||'"'
                        loop
                            -- 获取序列关联的 serial 字段表
                            select max('"'||c.relname||'"."'||a.attname||'"') into l_temp_result
                              from pg_attrdef as d
                              join pg_class as c on d.adrelid = c.oid
                              join pg_attribute as a on d.adrelid = a.attrelid and d.adnum = a.attnum
                              join pg_namespace as n on c.relnamespace = n.oid
                             where d.adsrc = 'nextval('''||name||'''::regclass)'
                               and n.nspname = schema;

                            -- 拼接序列创建语句
                            l_ddl := 'CREATE SEQUENCE "'||schema||'"."'||name||'"'
                                  || ' INCREMENT BY '||l_temp_record.increment_by
                                  || ' MINVALUE '||l_temp_record.min_value
                                  || ' MAXVALUE '||l_temp_record.max_value
                                  || ' START WITH '|| l_temp_record.start_value
                                  || ' CACHE '|| l_temp_record.cache_value
                                  || case l_temp_record.is_cycled when 'Y' THEN ' CYCLE' else ' NOCYCLE' end
                                  || case l_temp_result when null then ';' else ' OWNED BY '||l_temp_result||';' end;
                        end loop;
                    elsif object_type = 'synonym'
                    then
                        select 'CREATE OR REPLACE SYNONYM "'||schema||'"."'||name||'" FOR "'||synobjschema||'"."'||synobjname||'";'
                          into l_ddl
                          from pg_synonym
                         where synname = name
                           and synnamespace = (select oid
                                                 from pg_namespace
                                                where nspname = schema);
                    else
                        raise warning 'GET_DDL for type [%] does not supported yet', object_type;
                    end if;
                exception
                    when others then
                        raise exception '%', initcap(replace(object_type, '_', ' '))||' "'||schema||'"."'||name||'" does not exist !';
                end;

                return l_ddl;
            end;
            $$ LANGUAGE plpgsql;
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_metadata.get_ddl(varchar,varchar,varchar,varchar,varchar,varchar): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_output.enable(int4)
    -- =========================================================================
    begin
        if oracle.drop_compat_package('PROCEDURE', 'dbms_output', 'enable(int4)', '1.0')
        then
            execute 'CREATE OR REPLACE PROCEDURE dbms_output.enable(buffer_size int default 2000) AS
            BEGIN
                -- 创建临时表，用于缓存输出结果
                CREATE temporary table if not exists dbms_output_buffer
                (msg_content  text)
                ON COMMIT PRESERVE ROWS;
                INSERT INTO dbms_output_buffer values ('''');
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_output.enable(buffer_size int default 2000): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_output.disable()
    -- =========================================================================
    begin
        if oracle.drop_compat_package('PROCEDURE', 'dbms_output', 'disable()', '1.0')
        then
            execute 'CREATE OR REPLACE PROCEDURE dbms_output.disable() AS
            BEGIN
                for x in select count(*) cnt
                           from pg_class
                          where relname = ''dbms_output_buffer''
                            and relpersistence = ''t''
                         having count(*) >= 1
                loop
                    drop table dbms_output_buffer;
                end loop;
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_output.disable(): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_output.get_line(text,int4)
    -- =========================================================================
    begin
        if oracle.drop_compat_package('PROCEDURE', 'dbms_output', 'get_line(text,int4)', '1.0')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_output.get_line ( line    OUT text
                                                             , status  OUT INTEGER) AS
            DECLARE
                l_cnt     bigint;
            BEGIN
                select count(*) into l_cnt
                  from pg_class
                 where relname = ''dbms_output_buffer''
                   and relpersistence = ''t'';

                if l_cnt = 0  -- 缓存数据表不存在
                then
                    line := '''';
                    status := 1;
                else
                    select case when instr(msg_content, chr(10)) = 0 then msg_content
                                else substr(msg_content, 1, instr(msg_content, chr(10)) - 1)
                           end into line
                      from dbms_output_buffer
                     limit 1;

                    if line is null or line = ''''
                    then
                        status := 1;
                    else
                        status := 0;
                    end if;

                    -- 丢弃剩余数据，刷新缓存
                    truncate table dbms_output_buffer;
                    insert into dbms_output_buffer values ('''');
                end if;
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_output.get_line(text,int4): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_output.get_lines(text,int4)
    -- =========================================================================
    begin
        if oracle.drop_compat_package('PROCEDURE', 'dbms_output', 'get_lines(text,int4)', '1.0')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_output.get_lines ( lines     OUT    text
                                                              , numlines  IN OUT INTEGER) AS
            DECLARE
                l_cnt        bigint;
            BEGIN
                select count(*) into l_cnt
                  from pg_class
                 where relname = ''dbms_output_buffer''
                   and relpersistence = ''t'';

                if l_cnt = 0  -- 缓存数据表不存在
                then
                    lines := '''';
                    numlines := 0;
                else
                    select case when instr(msg_content, chr(10), 1, numlines) = 0 then msg_content
                                else substr(msg_content, 1, instr(msg_content, chr(10), 1, numlines) - 1)
                           end into lines
                      from dbms_output_buffer
                     limit 1;

                    if lines is null or lines = ''''
                    then
                        numlines := 0;
                    else
                        numlines := length(lines) - length(replace(lines, chr(10), ''''));
                        -- 不是一换行结尾，行数 +1 （最后一个无换行符的行，也作为一行内容）
                        if substr(lines, length(lines), 1) != chr(10)
                        then
                            numlines := numlines + 1;
                        end if;
                    end if;

                    -- 丢弃剩余数据，刷新缓存
                    truncate table dbms_output_buffer;
                    insert into dbms_output_buffer values ('''');
                end if;
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_output.get_lines(text,int4): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_output.new_line()
    -- =========================================================================
    begin
        if oracle.drop_compat_package('PROCEDURE', 'dbms_output', 'new_line()', '1.0')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_output.new_line() AS
            DECLARE
                l_cnt        bigint;
            BEGIN
                select count(*) into l_cnt
                  from pg_class
                 where relname = ''dbms_output_buffer''
                   and relpersistence = ''t'';

                if l_cnt > 0
                then
                    update dbms_output_buffer
                       set msg_content = msg_content||chr(10);
                end if;
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_output.new_line(): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_output.put(text)
    -- =========================================================================
    begin
        if oracle.drop_compat_package('PROCEDURE', 'dbms_output', 'put(text)', '1.1')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_output.put(item text) AS
            DECLARE
                l_cnt        bigint;
            BEGIN
                select count(*) into l_cnt
                  from pg_class
                 where relname = ''dbms_output_buffer''
                   and relpersistence = ''t'';

                if l_cnt > 0
                then
                    update dbms_output_buffer
                       set msg_content = msg_content||item;
                end if;

                raise notice ''%'', item;
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_output.put(text): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_output.put_line(text)
    -- =========================================================================
    begin
        if oracle.drop_compat_package('PROCEDURE', 'dbms_output', 'put_line(text)', '1.1')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_output.put_line(item text) AS
            DECLARE
                l_cnt        bigint;
            BEGIN
                select count(*) into l_cnt
                  from pg_class
                 where relname = ''dbms_output_buffer''
                   and relpersistence = ''t'';

                if l_cnt > 0
                then
                    update dbms_output_buffer
                       set msg_content = msg_content||item||chr(10);
                end if;

                raise notice ''%'', item;
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_output.put_line(text): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_random.initialize(int4)
    -- =========================================================================
    begin
        if oracle.drop_compat_package('PROCEDURE', 'dbms_random', 'initialize(int4)', '1.0')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_random.initialize(val int) AS
            BEGIN
                raise notice ''This procedure is deprecated with Release 11gR1, it should not be used.'';
                raise notice ''Nothing will be done!'';
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_random.initialize(int4): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_random.normal()
    -- 利用 Box-Muller 变换算法通过两个平均分布的随机数生成正态分布的随机数（效率不高）
    -- =========================================================================
    begin
        if oracle.drop_compat_package('FUNCTION', 'dbms_random', 'normal()', '1.0', 'sql')
        then
            execute '
            CREATE OR REPLACE FUNCTION dbms_random.normal()
            returns numeric AS $$
            select (sqrt(-2 * ln(random())) * cos(2 * pi() * random()))::numeric;
            $$ language sql;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_random.normal(): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_random.random()
    -- =========================================================================
    begin
        if oracle.drop_compat_package('FUNCTION', 'dbms_random', 'random()', '1.0')
        then
            execute '
            CREATE OR REPLACE FUNCTION dbms_random.random()
            returns int AS $$
            BEGIN
                raise notice ''This function is deprecated with Release 11gR1, although currently supported, it should not be used.'';
                return case when random() >= 0.5 then power(2,31) * random() else 0 - power(2,31) * random() end;
            END;
            $$ language plpgsql';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_random.random(): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_random.seed(int4)
    -- PACKAGE 参数表明支持重载
    -- =========================================================================
    begin
        if oracle.drop_compat_package('PROCEDURE', 'dbms_random', 'seed(int4)', '1.0')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_random.seed(val int) PACKAGE AS
            BEGIN
                setseed(val / power(2,31));
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_random.seed(int4): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_random.seed(text)
    -- PACKAGE 参数表明支持重载
    -- =========================================================================
    begin
        if oracle.drop_compat_package('PROCEDURE', 'dbms_random', 'seed(text)', '1.0')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_random.seed(val text) PACKAGE AS
            DECLARE
                l_ascii   numeric;
            BEGIN
                if val = '''' or val is null
                then
                    null;
                else
                    select sum(ascii(id)) / (127 * count(*))
                      into l_ascii
                      from unnest(string_to_array(val, '''')) as id;
                    if l_ascii <= 1
                    then
                        setseed(l_ascii);
                    end if;
                end if;
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_random.seed(text): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_random.string(char,int4)
    -- =========================================================================
    begin
        if oracle.drop_compat_package('FUNCTION', 'dbms_random', 'string(bpchar,int4)', '1.0', 'sql')
        then
            execute '
            CREATE OR REPLACE FUNCTION dbms_random.string(opt  char,len  int4)
            returns text AS $$
            select case upper(opt)
            -- 全小写随机
            when ''L'' then (select string_agg(chr(id), '''') from (select 96 + ceil(26 * random()) as id from generate_series(1, len)))
            -- 大小写混合随机
            when ''A'' then (select string_agg((array[''A'',''B'',''C'',''D'',''E'',''F'',''G'',''H'',''I'',''J'',''K'',''L'',''M'',''N'',''O'',''P'',''Q'',''R'',''S'',''T'',''U'',''V'',''W'',''X'',''Y'',''Z'',''a'',''b'',''c'',''d'',''e'',''f'',''g'',''h'',''i'',''j'',''k'',''l'',''m'',''n'',''o'',''p'',''q'',''r'',''s'',''t'',''u'',''v'',''w'',''x'',''y'',''z''])[id], '''') from (select ceil(52 * random()) as id from generate_series(1, len)))
            -- 大小写+数字混合随机
            when ''X'' then (select string_agg((array[''0'',''1'',''2'',''3'',''4'',''5'',''6'',''7'',''8'',''9'',''A'',''B'',''C'',''D'',''E'',''F'',''G'',''H'',''I'',''J'',''K'',''L'',''M'',''N'',''O'',''P'',''Q'',''R'',''S'',''T'',''U'',''V'',''W'',''X'',''Y'',''Z'',''a'',''b'',''c'',''d'',''e'',''f'',''g'',''h'',''i'',''j'',''k'',''l'',''m'',''n'',''o'',''p'',''q'',''r'',''s'',''t'',''u'',''v'',''w'',''x'',''y'',''z''])[id], '''') from (select ceil(62 * random()) as id from generate_series(1, len)))
            -- 全可见字符随机
            when ''P'' then (select string_agg(chr(id), '''') from (select 31 + ceil(95 * random()) as id from generate_series(1, len)))
            -- 全大写随机
            else (select string_agg(chr(id), '''') from (select 64 + ceil(26 * random()) as id from generate_series(1, len)))
            end;
            $$ language sql';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_random.string(char,int4): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_random.terminate()
    -- =========================================================================
    begin
        if oracle.drop_compat_package('PROCEDURE', 'dbms_random', 'terminate()', '1.0')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_random.terminate() AS
            BEGIN
                raise notice ''This procedure is deprecated with Release 11gR1, it should not be used.'';
                raise notice ''Nothing will be done!'';
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_random.terminate(): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_random.value()
    -- =========================================================================
    begin
        if oracle.drop_compat_package('FUNCTION', 'dbms_random', 'value()', '1.0', 'sql')
        then
            execute '
            CREATE OR REPLACE FUNCTION dbms_random.value()
            returns numeric AS $$
            select random()::numeric;
            $$ language sql;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_random.value(): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_random.value(numeric,numeric)
    -- =========================================================================
    begin
        if oracle.drop_compat_package('FUNCTION', 'dbms_random', 'value(numeric,numeric)', '1.0', 'sql')
        then
            execute '
            CREATE OR REPLACE FUNCTION dbms_random.value(low  numeric, high  numeric)
            returns numeric AS $$
            select (random() * (high - low) + low)::numeric;
            $$ language sql;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_random.value(numeric,numeric): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_job.broken(int8,bool,timestamp)
    -- =========================================================================
    begin
        if oracle.drop_compat_package('PROCEDURE', 'dbms_job', 'broken(int8,bool,timestamp)', '1.0')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_job.broken ( job       int8
                                                        , broken    bool
                                                        , next_date timestamp default sysdate)
            AS
            BEGIN
                perform pkg_service.job_finish(job, broken, next_date);
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_job.broken(int8,bool,timestamp): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_job.change(int8,text,timestamp,text,int4,bool)
    -- =========================================================================
    begin
        if oracle.drop_compat_package('PROCEDURE', 'dbms_job', 'change(int8,text,timestamp,text,int4,bool)', '1.0')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_job.change ( job         int8
                                                        , what        text
                                                        , next_date   timestamp
                                                        , "interval"  text
                                                        , instance    int4      default null
                                                        , force       bool      default ''false'')
            AS
            BEGIN
                if instance is not null
                then
                    raise notice ''Parameter "instance" does nothing in this procedure !'';
                end if;
                if force
                then
                    raise notice ''Parameter "force" does nothing in this procedure !'';
                end if;
                perform pkg_service.job_update(job, next_date, interval, what);
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_job.change(int8,text,timestamp,text,int4,bool): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_job.instance(int8,int4,bool)
    -- =========================================================================
    begin
        if oracle.drop_compat_package('PROCEDURE', 'dbms_job', 'instance(int8,int4,bool)', '1.0')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_job.instance ( job       int8
                                                          , instance  int4
                                                          , force     bool  default ''false'')
            AS
            BEGIN
                raise notice ''Nothing to do in this procedure !'';
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_job.instance(int8,int4,bool): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_job.interval(int8,text)
    -- =========================================================================
    begin
        if oracle.drop_compat_package('PROCEDURE', 'dbms_job', 'interval(int8,text)', '1.0')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_job.interval ( job           int8
                                                          , "interval"    text)
            AS
            DECLARE
                l_next_date     timestamp;
                l_what          text;
            BEGIN
                select next_run_date into l_next_date
                  from pg_job
                 where job_id = job;
                select what into l_what
                  from pg_job_proc
                 where job_id = job;
                perform pkg_service.job_update(job, l_next_date, interval, l_what);
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_job.interval(int8,text): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_job.next_date(int8,timestamp)
    -- =========================================================================
    begin
        if oracle.drop_compat_package('PROCEDURE', 'dbms_job', 'next_date(int8,timestamp)', '1.0')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_job.next_date ( job        int8
                                                           , next_date  timestamp)
            AS
            DECLARE
                l_interval    text;
                l_what        text;
            BEGIN
                select interval into l_interval
                  from pg_job
                 where job_id = job;
                select what into l_what
                  from pg_job_proc
                 where job_id = job;
                perform pkg_service.job_update(job, next_date, l_interval, l_what);
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_job.next_date(int8,timestamp): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_job.remove(int8)
    -- =========================================================================
    begin
        if oracle.drop_compat_package('PROCEDURE', 'dbms_job', 'remove(int8)', '1.0')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_job.remove(job int8)
            AS
            BEGIN
                perform pkg_service.job_cancel(job);
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_job.remove(int8): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_job.run(int8,bool)
    -- =========================================================================
    begin
        if oracle.drop_compat_package('PROCEDURE', 'dbms_job', 'run(int8,bool)', '1.0')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_job.run( job    int8
                                                    , force  boolean default ''false'')
            AS
            DECLARE
                l_interval    text;
                l_next_date   timestamp;
                l_what        text;
            BEGIN
                if force
                then
                    raise notice ''Parameter "force" does nothing in this procedure !'';
                end if;
                select interval into l_interval
                  from pg_job
                 where job_id = job;
                select what into l_what
                  from pg_job_proc
                 where job_id = job;
                execute immediate ''select ''||l_interval into l_next_date;
                execute immediate l_what;
                perform pkg_service.job_update(job, l_next_date, l_interval, l_what);
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_job.run(int8,bool): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_job.submit(int8,text,timestamp,text,bool,int4,bool)
    -- =========================================================================
    begin
        if oracle.drop_compat_package('PROCEDURE', 'dbms_job', 'submit(int8,text,timestamp,text,bool,int4,bool)', '1.0')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_job.submit ( job         out int8
                                                        , what        in  text
                                                        , next_date   in  timestamp default sysdate
                                                        , "interval"  in  text      default ''null''
                                                        , no_parse    in  bool      default ''false''
                                                        , instance    in  int4      default null
                                                        , force       in  bool      default ''false'')
            AS
            BEGIN
                if no_parse
                then
                    raise notice ''Parameter "no_parse" does nothing in this procedure !'';
                end if;
                if instance is not null
                then
                    raise notice ''Parameter "instance" does nothing in this procedure !'';
                end if;
                if force
                then
                    raise notice ''Parameter "force" does nothing in this procedure !'';
                end if;
                job := pkg_service.job_submit(null, what, next_date, interval);
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_job.submit(int8,text,timestamp,text,bool,int4,bool): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_job.user_export(int8,text)
    -- =========================================================================
    begin
        if oracle.drop_compat_package('PROCEDURE', 'dbms_job', 'user_export(int8,text)', '1.0')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_job.user_export ( job    in     int8
                                                             , mycall inout  text)
            AS
            BEGIN
                select ''select pkg_service.job_submit(id=>''
                    || j.job_id
                    || '', content=>''
                    || chr(39) || p.what || chr(39)
                    || '', next_time=>to_timestamp('' || chr(39) || to_char(next_run_date, ''yyyy-mm-dd hh24:mi:ss'') || chr(39)
                                                 || '', '' || chr(39) || ''yyyy-mm-dd hh24:mi:ss'' || chr(39)
                    || ''), interval_time=>''||chr(39)||interval||chr(39)||'');''
                  into mycall
                  from pg_job as j
                  join pg_job_proc as p on j.job_id = p.job_id
                 where j.job_id = job;
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_job.user_export(int8,text): %', SQLERRM;
            rollback;
    end;


    -- =========================================================================
    -- dbms_job.what(int8,text)
    -- =========================================================================
    begin
        if oracle.drop_compat_package('PROCEDURE', 'dbms_job', 'what(int8,text)', '1.0')
        then
            execute '
            CREATE OR REPLACE PROCEDURE dbms_job.what ( job   int8
                                                      , what  text)
            AS
            DECLARE
                l_next_date     timestamp;
                l_interval      text;
            BEGIN
                select next_run_date, interval
                  into l_next_date, l_interval
                  from pg_job
                 where job_id = job;
                perform pkg_service.job_update(job, l_next_date, l_interval, what);
            END;';
        end if;
        commit;
    exception
        when others then
            RAISE WARNING 'Error in dbms_job.what(int8,text): %', SQLERRM;
            rollback;
    end;

end;
$PKG_CREATION$ language plpgsql;

-- Show result & Exit
select * from temp_result where exists (select 1 from pg_settings where name = 'application_name' and setting = 'gsql');
\q
